package com.example.demo;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileInputStream;
import java.io.InputStream;
import java.net.URLEncoder;
import java.security.MessageDigest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.*;

import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.util.EntityUtils;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DemoApplication {

    // 百度翻译 API 配置（请替换为自己的）
    private static final String APP_ID = "20250820002435146";
    private static final String SECRET = "VwrEZiTXcZVgPrh1cfaj";
    private static final String API_URL = "https://api.fanyi.baidu.com/api/trans/vip/translate";

    public static void main(String[] args) throws Exception {
        // 1. 读取 Excel 表头
        String excelPath = "D:\\Users\\Administrator\\Desktop\\8.20\\uf_xmda.xls";  // 修改为你的文件路径
        List<String> headers = readExcelHeader(excelPath);

        // 2. 翻译并生成唯一列名
        List<String> columns = generateUniqueEnglishColumns(headers);

        // 3. 读取每列最大长度 → 决定数据库字段类型
        List<Integer> maxLens = getMaxColumnLength(excelPath, headers.size());

        // 4. 拼接建表 SQL（动态长度）
        String tableName = "uf_xmda02";
        String createTableSql = buildCreateTableSql(tableName, columns, maxLens);

        System.out.println("建表 SQL:");
        System.out.println(createTableSql);

        String url = "jdbc:mysql://localhost:3306/uf_xmda01?allowPublicKeyRetrieval=true&useSSL=false&characterEncoding=UTF-8";
        String user = "root";
        String password = "123456";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement()) {

            // 5. 创建表
            stmt.executeUpdate("DROP TABLE IF EXISTS `" + tableName + "`");
            stmt.executeUpdate(createTableSql);
            System.out.println("建表成功！");

            // 6. 插入数据
            insertExcelData(conn, tableName, columns, excelPath);
            System.out.println("数据插入完成！");
        }
    }

    // 读取 Excel 首行表头（兼容 XLS / XLSX）
    private static List<String> readExcelHeader(String excelPath) throws Exception {
        List<String> headers = new ArrayList<>();
        try (InputStream is = new FileInputStream(excelPath)) {
            Workbook workbook;
            if (excelPath.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(is);
            } else if (excelPath.endsWith(".xls")) {
                workbook = new HSSFWorkbook(is);
            } else {
                throw new IllegalArgumentException("不支持的文件格式: " + excelPath);
            }
            Sheet sheet = workbook.getSheetAt(0);
            Row row = sheet.getRow(0);
            for (Cell cell : row) {
                headers.add(cell.toString().trim());
            }
        }
        return headers;
    }

    // 调用百度翻译 API
    private static String translateToEnglish(String text) throws Exception {
        String salt = String.valueOf(System.currentTimeMillis());
        String sign = md5(APP_ID + text + salt + SECRET);
        String url = API_URL + "?q=" + URLEncoder.encode(text, "UTF-8") +
                "&from=zh&to=en&appid=" + APP_ID +
                "&salt=" + salt + "&sign=" + sign;

        try (CloseableHttpClient httpClient = HttpClients.createDefault()) {
            HttpGet request = new HttpGet(url);
            try (CloseableHttpResponse response = httpClient.execute(request)) {
                String json = EntityUtils.toString(response.getEntity(), "UTF-8");

                ObjectMapper mapper = new ObjectMapper();
                JsonNode root = mapper.readTree(json);
                if (root.has("trans_result")) {
                    String dst = root.get("trans_result").get(0).get("dst").asText();
                    return cleanColumnName(dst);
                }
                return "col_" + System.currentTimeMillis();
            }
        }
    }

    // 生成唯一英文列名
    private static List<String> generateUniqueEnglishColumns(List<String> headers) throws Exception {
        Set<String> used = new HashSet<>();
        List<String> result = new ArrayList<>();

        for (String h : headers) {
            String en = translateToEnglish(h);
            String unique = en;
            int cnt = 1;
            while (used.contains(unique)) {
                unique = en + "_" + cnt;
                cnt++;
            }
            used.add(unique);
            result.add(unique);
        }
        return result;
    }

    // 清理列名：小写、字母数字下划线
    private static String cleanColumnName(String name) {
        return name.replaceAll("\\s+", "_")
                .replaceAll("[^a-zA-Z0-9_]", "_")
                .toLowerCase();
    }

    // 读取每列最大长度 → 适配建表字段
    private static List<Integer> getMaxColumnLength(String excelPath, int colCount) throws Exception {
        List<Integer> maxLens = new ArrayList<>(Collections.nCopies(colCount, 0));
        try (InputStream is = new FileInputStream(excelPath)) {
            Workbook workbook;
            if (excelPath.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(is);
            } else {
                workbook = new HSSFWorkbook(is);
            }
            Sheet sheet = workbook.getSheetAt(0);
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) continue;
                for (int c = 0; c < colCount; c++) {
                    Cell cell = row.getCell(c);
                    if (cell != null) {
                        cell.setCellType(CellType.STRING);
                        int len = cell.getStringCellValue().length();
                        maxLens.set(c, Math.max(maxLens.get(c), len));
                    }
                }
            }
        }
        return maxLens;
    }

    // 拼接建表 SQL（动态长度）
    private static String buildCreateTableSql(String tableName, List<String> columns, List<Integer> maxLens) {
        StringBuilder sb = new StringBuilder("CREATE TABLE `" + tableName + "` (");
        for (int i = 0; i < columns.size(); i++) {
            int len = maxLens.get(i);
            String type = (len > 255) ? "TEXT" : "VARCHAR(" + (len == 0 ? 255 : len + 10) + ")";
            sb.append("`").append(columns.get(i)).append("` ").append(type);
            if (i < columns.size() - 1) sb.append(", ");
        }
        sb.append(")");
        return sb.toString();
    }

    // 插入 Excel 数据
    private static void insertExcelData(Connection conn, String tableName, List<String> columns, String excelPath) throws Exception {
        try (InputStream is = new FileInputStream(excelPath)) {
            Workbook workbook;
            if (excelPath.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(is);
            } else {
                workbook = new HSSFWorkbook(is);
            }
            Sheet sheet = workbook.getSheetAt(0);

            StringBuilder sb = new StringBuilder("INSERT INTO `" + tableName + "` (");
            for (int i = 0; i < columns.size(); i++) {
                sb.append("`").append(columns.get(i)).append("`");
                if (i < columns.size() - 1) sb.append(", ");
            }
            sb.append(") VALUES (");
            for (int i = 0; i < columns.size(); i++) {
                sb.append("?");
                if (i < columns.size() - 1) sb.append(", ");
            }
            sb.append(")");

            String insertSql = sb.toString();
            try (PreparedStatement pstmt = conn.prepareStatement(insertSql)) {
                for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                    Row row = sheet.getRow(r);
                    if (row == null) continue;
                    for (int c = 0; c < columns.size(); c++) {
                        Cell cell = row.getCell(c);
                        if (cell != null) {
                            cell.setCellType(CellType.STRING);
                            pstmt.setString(c + 1, cell.getStringCellValue());
                        } else {
                            pstmt.setString(c + 1, null);
                        }
                    }
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
            }
        }
    }

    // 生成 MD5
    private static String md5(String input) throws Exception {
        MessageDigest md = MessageDigest.getInstance("MD5");
        byte[] array = md.digest(input.getBytes("UTF-8"));
        StringBuilder sb = new StringBuilder();
        for (byte b : array) {
            sb.append(String.format("%02x", b & 0xff));
        }
        return sb.toString();
    }
}
